using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;


using Material.Object;
using Material.DAL;
namespace Material.Entity
{
	public class TblKelompokEntity
	{
		private Accessor accessor;
		private string connString;

		public TblKelompokEntity (string _connString)
		{
			 this.connString = _connString;
		}

		public int Insert(TblKelompok tblkelompok)
		{
            StringBuilder sql = new StringBuilder();
            
            sql.AppendLine(" INSERT INTO [TblKelompok] (");
            sql.AppendLine(" [Kelompok]");
            sql.AppendLine(" ,[Keterangan]");
            sql.AppendLine(" ,[CreatedDate]");
            sql.AppendLine(" ,[CreatedBy]");
            sql.AppendLine(" )");
            sql.AppendLine(" VALUES (");
            sql.AppendLine("  @Kelompok");
            sql.AppendLine(" ,@Keterangan");
            sql.AppendLine(" ,@CreatedDate");
            sql.AppendLine(" ,@CreatedBy");
            sql.AppendLine(" )");

			 int result = -1;
			 accessor = new Accessor(true, connString);
			 accessor.CommandText = sql.ToString();
			 accessor.CommandType = CommandType.Text;

			 accessor.Add("@Kelompok" ,tblkelompok.Kelompok );
			 accessor.Add("@Keterangan" ,tblkelompok.Keterangan );
			 accessor.Add("@CreatedDate" ,tblkelompok.CreatedDate );
			 accessor.Add("@CreatedBy" ,tblkelompok.CreatedBy );


			 result = Excutor.ExecuteNonQuery(accessor);
			 if (result != -1)
				 accessor.CommitTransaction();
			 else
				 accessor.RollBackTransaction();
			 return result;
		}

		public int Update(TblKelompok tblkelompok)
		{
            StringBuilder sql = new StringBuilder();
            sql.AppendLine(" UPDATE [TblKelompok]");
            sql.AppendLine(" SET [Kelompok] =@Kelompok");
            sql.AppendLine(",[Keterangan] =@Keterangan");
            sql.AppendLine(",[ModifiedDate] =@ModifiedDate");
            sql.AppendLine(",[ModifiedBy] = @ModifiedBy");
            sql.AppendLine(" WHERE Id = @Id");


			 int result = -1;
			 accessor = new Accessor(true, connString);
             accessor.CommandText = sql.ToString();
			 accessor.CommandType = CommandType.Text;

			 accessor.Add(@"Id" ,tblkelompok.Id );
			 accessor.Add(@"Kelompok" ,tblkelompok.Kelompok );
			 accessor.Add(@"Keterangan" ,tblkelompok.Keterangan );
			 accessor.Add(@"ModifiedDate" ,tblkelompok.ModifiedDate );
			 accessor.Add(@"ModifiedBy" ,tblkelompok.ModifiedBy );

			 result = Excutor.ExecuteNonQuery(accessor);
			 if (result != -1)
				 accessor.CommitTransaction();
			 else
				 accessor.RollBackTransaction();
			 return result;
		}

		public int Delete(TblKelompok tblkelompok)
		{
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("  DELETE FROM [TblKelompok]");
            sql.AppendLine("  WHERE Id = @Id");


			 int result = -1;
			 accessor = new Accessor(true, connString);
             accessor.CommandText = sql.ToString();
			 accessor.CommandType = CommandType.Text;
			 accessor.Add(@"Id" ,tblkelompok.Id);

			 result = Excutor.ExecuteNonQuery(accessor);
			 if (result != -1)
				 accessor.CommitTransaction();
			 else
				 accessor.RollBackTransaction();
			 return result;
		}

		public TblKelompok GetDataByID(TblKelompok tblkelompok)
		{

            StringBuilder sql = new StringBuilder();
            sql.AppendLine(" SELECT");
            sql.AppendLine(" [Id]");
            sql.AppendLine(" ,[Kelompok]");
            sql.AppendLine(" ,[Keterangan]");
            sql.AppendLine(" ,[CreatedDate]");
            sql.AppendLine(" ,[CreatedBy]");
            sql.AppendLine(" ,[ModifiedDate]");
            sql.AppendLine(" ,[ModifiedBy]");
            sql.AppendLine(" FROM  [TblKelompok]");
            sql.AppendLine(" WHERE Id = @Id");
            sql.AppendLine(" ORDER BY Kelompok ASC");
           


			 TblKelompok result = new TblKelompok();
			 accessor = new Accessor(true, connString);
			 accessor.CommandText = sql.ToString();
			 accessor.CommandType = CommandType.Text;
			 accessor.Add(@"Id" ,tblkelompok.Id);

			 SqlDataReader rdr = Excutor.GetDataReader(accessor);
			 if (rdr.HasRows)
			 {
				 while (rdr.Read())
					 {
						 result.Id = rdr.GetInt32(0);
						 result.Kelompok = rdr.GetString(1);
						 result.Keterangan = rdr.GetString(2);
						 result.CreatedDate = rdr.GetDateTime(3);
						 result.CreatedBy = rdr.GetString(4);
                         if (rdr[5] != DBNull.Value)
						    result.ModifiedDate = rdr.GetDateTime(5);
                        if (rdr[6] != DBNull.Value)
						 result.ModifiedBy = rdr.GetString(6);

                        
                             
					  }
			}
			 return result;
		}

		public List<TblKelompok> GetDataAll()
		{
            StringBuilder sql = new StringBuilder();
            sql.AppendLine(" SELECT");
            sql.AppendLine(" [Id]");
            sql.AppendLine(" ,[Kelompok]");
            sql.AppendLine(" ,[Keterangan]");
            sql.AppendLine(" ,[CreatedDate]");
            sql.AppendLine(" ,[CreatedBy]");
            sql.AppendLine(" ,[ModifiedDate]");
            sql.AppendLine(" ,[ModifiedBy]");
            sql.AppendLine(" FROM  [TblKelompok]");
            sql.AppendLine(" ORDER BY Kelompok ASC");

			 List<TblKelompok> result = new List<TblKelompok>();
			 accessor = new Accessor(true, connString);
			 accessor.CommandText =sql.ToString();
			 accessor.CommandType = CommandType.Text;

			 SqlDataReader rdr = Excutor.GetDataReader(accessor);
			 if (rdr.HasRows)
			 {
				 while (rdr.Read())
					 {
						 TblKelompok obj = new TblKelompok();
						 obj.Id = rdr.GetInt32(0);
						 obj.Kelompok = rdr.GetString(1);
						 obj.Keterangan = rdr.GetString(2);
						 obj.CreatedDate = rdr.GetDateTime(3);
						 obj.CreatedBy = rdr.GetString(4);
                         if(rdr[5]!=DBNull.Value)
						    obj.ModifiedDate = rdr.GetDateTime(5);
                        if (rdr[6] != DBNull.Value)
						    obj.ModifiedBy = rdr.GetString(6);
						 result.Add(obj);
					  }
			}
			 return result;
		}

        public SqlCommand GetCommandReport()
        {

            StringBuilder sql = new StringBuilder();
            sql.AppendLine(" SELECT");
            sql.AppendLine(" [Id]");
            sql.AppendLine(" ,[Kelompok]");
            sql.AppendLine(" ,[Keterangan]");
            sql.AppendLine(" ,[CreatedDate]");
            sql.AppendLine(" ,[CreatedBy]");
            sql.AppendLine(" ,[ModifiedDate]");
            sql.AppendLine(" ,[ModifiedBy]");
            sql.AppendLine(" FROM  [TblKelompok]");
            sql.AppendLine(" ORDER BY Kelompok ASC");


            SqlCommand comm = new SqlCommand();

            accessor = new Accessor(true, connString);
            accessor.CommandText = sql.ToString();
            accessor.CommandType = CommandType.Text;
            comm = accessor.Command;

            return comm;
        }


        public DataSet GetFindData(TblKelompok tblKelompok)
        {
            DataSet ds = new DataSet();
            StringBuilder sql = new StringBuilder();
            sql.AppendLine(" SELECT");
            sql.AppendLine(" [Id]");
            sql.AppendLine(" ,[Kelompok]");
            sql.AppendLine(" ,[Keterangan]");
            sql.AppendLine(" ,[CreatedDate]");
            sql.AppendLine(" ,[CreatedBy]");
            sql.AppendLine(" ,[ModifiedDate]");
            sql.AppendLine(" ,[ModifiedBy]");
            sql.AppendLine(" FROM  [TblKelompok]");
            sql.AppendLine("WHERE Kelompok LIKE '%" + tblKelompok.Kelompok + "%'");
            sql.AppendLine("ORDER BY KELOMPOK ASC");

            accessor = new Accessor(true, connString);
            accessor.CommandText = sql.ToString();
            accessor.CommandType = CommandType.Text;

            ds = Excutor.GetDataSet(accessor);

            return ds;

        }

	}
}
